package com.doeis.core;

import java.beans.PropertyDescriptor;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;

import com.doeis.core.annotation.Associate;
import com.doeis.core.annotation.Table;
import com.doeis.core.annotation.Transaction;
import com.doeis.core.bean.Entity;
import com.doeis.core.db.DBException;
import com.doeis.core.db.DBHelper;
import com.doeis.core.db.DBManager;
import com.doeis.core.db.DaoException;
import com.doeis.core.db.Page;
import com.doeis.manage.bean.User;
import com.doeis.tools.BeanUtils;

public class Dao extends DBHelper {
	private static final Logger log = Logger.getLogger(Dao.class);

	public <T> T readById(String dataSourceName, Class<T> clazz, Object id) throws DaoException {
		return readById(dataSourceName, clazz, id, Boolean.valueOf(false));
	}

	public <T> T readById(Class<T> clazz, Object id) throws DaoException {
		return readById(DBManager.getDefaultDataSourceName(Boolean.TRUE.booleanValue()), clazz, id);
	}

	public <T> T readById(String dataSourceName, Class<T> clazz, Object id, Boolean doAssociate) throws DaoException {
		return readByCondition(true, dataSourceName, clazz, "id=?", doAssociate, new Object[] { id });
	}

	public <T> T readById(Class<T> clazz, Object id, Boolean doAssociate) throws DaoException {
		return readById(DBManager.getDefaultDataSourceName(Boolean.TRUE.booleanValue()), clazz, id, doAssociate);
	}

	public <T> T readByCondition(String dataSourceName, Class<T> clazz, String condition, Object[] params) throws DaoException {
		return readByCondition(dataSourceName, clazz, condition, Boolean.valueOf(false), params);
	}

	public <T> T readByCondition(Class<T> clazz, String condition, Object[] params) throws DaoException {
		return readByCondition(DBManager.getDefaultDataSourceName(Boolean.TRUE.booleanValue()), clazz, condition, params);
	}

	public <T> T readByCondition(boolean isReadById, String dataSourceName, Class<T> clazz, String condition, Boolean doAssociate, Object[] params)
			throws DaoException {
		String tableName = getTableName(clazz);
		if (tableName == null) {
			throw new DaoException("get failed：" + clazz + " tableName is null");
		}
		StringBuffer sql = new StringBuffer("select * from ");
		sql.append(tableName).append(" where ").append(condition);
		T t = read(dataSourceName, clazz, sql.toString(), params);
		if (doAssociate.booleanValue()) {
			associate(clazz, t, doAssociate);
		}
		return t;
	}

	public <T> T readByCondition(String dataSourceName, Class<T> clazz, String condition, Boolean doAssociate, Object[] params) throws DaoException {
		return readByCondition(false, dataSourceName, clazz, condition, doAssociate, params);
	}

	public <T> T readByCondition(Class<T> clazz, String condition, Boolean doAssociate, Object[] params) throws DaoException {
		return readByCondition(DBManager.getDefaultDataSourceName(Boolean.TRUE.booleanValue()), clazz, condition, doAssociate, params);
	}

	public <T> List<T> query(String dataSourceName, Class<T> clazz, String sql, Boolean doAssociate, Object[] params) throws DBException {
		List<T> results = query(dataSourceName, clazz, sql, params);
		if ((doAssociate.booleanValue()) && (results.size() > 0)) {
			for (T entity : results) {
				associate(clazz, entity, doAssociate);
			}
		}
		return results;
	}

	public <T> List<T> query(Class<T> clazz, String sql, Boolean doAssociate, Object[] params) throws DBException {
		return query(DBManager.getDefaultDataSourceName(Boolean.TRUE.booleanValue()), clazz, sql, doAssociate, params);
	}

	public <T> Page<T> querySlice(String dataSourceName, Class<T> clazz, String sql, Page<T> page, Object[] args) throws DaoException {
		return querySlice(dataSourceName, clazz, sql, page, Boolean.valueOf(false), args);
	}

	public <T> Page<T> querySlice(Class<T> clazz, String sql, Page<T> page, Object[] args) throws DaoException {
		return querySlice(DBManager.getDefaultDataSourceName(Boolean.TRUE.booleanValue()), clazz, sql, page, args);
	}

	@Transaction(level = 2, propagation = 0, rw = "r")
	public <T> Page<T> querySlice(String dataSourceName, Class<T> clazz, String sql, Page<T> page, Boolean doAssociate, Object[] args)
			throws DaoException {
		if ((page.getPage().intValue() < 0) || (page.getPer().intValue() < 0))
			throw new DBException("Illegal parameter of 'page' or 'count', Must be positive.");
		if (doAssociate == null)
			doAssociate = Boolean.valueOf(false);
		int totalCount = count(dataSourceName, getCountSql(sql), args).intValue();
		List<T> results = querySlice(dataSourceName, clazz, sql, page.getPage().intValue(), page.getPer().intValue(), args);
		page.setTotalRecords(totalCount);
		page.setResults(results);

		if ((doAssociate.booleanValue()) && (results.size() > 0)) {
			for (T entity : results) {
				associate(clazz, entity, doAssociate);
			}
		}

		return page;
	}

	public <T> Page<T> querySlice(Class<T> clazz, String sql, Page<T> page, Boolean doAssociate, Object[] args) throws DaoException {
		return querySlice(DBManager.getDefaultDataSourceName(Boolean.TRUE.booleanValue()), clazz, sql, page, doAssociate, args);
	}

	public boolean insert(String dataSourceName, Entity obj) throws DaoException {
		return execute(dataSourceName, obj, false, null, new Object[0]);
	}

	public boolean insert(Entity obj) throws DaoException {
		return insert(DBManager.getDefaultDataSourceName(Boolean.FALSE.booleanValue()), obj);
	}

	public Integer insertAndReturnKey(String dataSourceName, Entity entity) throws DaoException {
		if (entity == null) {
			throw new DaoException("Entity is null");
		}
		String executeMethod = "INSERT";
		String tableName = getTableName(entity.getClass());

		if (StringUtils.isBlank(tableName)) {
			throw new DaoException("table name is null where " + executeMethod + " with dao");
		}
		Integer key = null;
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		List<Object> params = new ArrayList<Object>();
		try {
			StringBuffer sqlBuffer = new StringBuffer(executeMethod);
			sqlBuffer.append(" ");
			sqlBuffer.append(tableName);
			sqlBuffer.append(" SET ");

			String fields = keyAndParams(entity, sqlBuffer, params);
			String sql = sqlBuffer.substring(0, sqlBuffer.length() - 2);

			Connection conn = getConnection(dataSourceName);
			pstmt = conn.prepareStatement(sql, 1);
			getQueryrunner().fillStatementWithBean(pstmt, entity, fields.split(","));
			pstmt.executeUpdate();
			rs = pstmt.getGeneratedKeys();
			if (rs.next())
				key = Integer.valueOf(rs.getInt(1));
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			throw new DaoException("create and execute " + executeMethod + " sql error", e);
		}
		return key;
	}

	public Integer insertAndReturnKey(Entity entity) throws DaoException {
		return insertAndReturnKey(DBManager.getDefaultDataSourceName(Boolean.FALSE.booleanValue()), entity);
	}

	public boolean update(String dataSourceName, Entity obj) throws DaoException {
		Object idValue = null;
		try {
			idValue = BeanUtils.getProperty(obj, "id");
			return update(dataSourceName, obj, "id = ?", new Object[] { idValue });
		} catch (Exception e) {
			throw new DaoException(e.getMessage(), e);
		}
	}

	public boolean update(Entity obj) throws DaoException {
		return update(DBManager.getDefaultDataSourceName(Boolean.FALSE.booleanValue()), obj);
	}

	public boolean update(String dataSourceName, Entity obj, String conditions, Object[] conditionValues) throws DaoException {
		return execute(dataSourceName, obj, true, conditions, conditionValues);
	}

	public boolean update(Entity obj, String conditions, Object[] conditionValues) throws DaoException {
		return update(DBManager.getDefaultDataSourceName(Boolean.FALSE.booleanValue()), obj, conditions, conditionValues);
	}

	public boolean delete(String dataSourceName, Entity obj) throws DaoException {
		Object idValue = null;
		try {
			idValue = BeanUtils.getProperty(obj, "id");
			return delete(dataSourceName, obj.getClass(), idValue);
		} catch (Exception e) {
			throw new DaoException(e.getMessage(), e);
		}
	}

	public boolean delete(Entity obj) throws DaoException {
		return delete(DBManager.getDefaultDataSourceName(Boolean.FALSE.booleanValue()), obj);
	}

	public boolean delete(String dataSourceName, Class<? extends Entity> entity, Object id) throws DaoException {
		return delete(dataSourceName, entity, "id = ?", new Object[] { id });
	}

	public boolean delete(Class<? extends Entity> entity, Object id) throws DaoException {
		return delete(DBManager.getDefaultDataSourceName(Boolean.FALSE.booleanValue()), entity, id);
	}

	public boolean delete(String dataSourceName, Class<? extends Entity> entity, String conditions, Object[] conditionValues) throws DaoException {
		String table = getTableName(entity);
		boolean f = false;
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("DELETE FROM " + table + " WHERE " + conditions);
			f = DBHelper.update(dataSourceName, sql.toString(), conditionValues) > 0;
		} catch (Exception e) {
			log.error(e.getCause(), e);
			throw new DaoException("delete FROM" + table + " sql error");
		}
		return f;
	}

	public boolean delete(Class<? extends Entity> entity, String conditions, Object[] conditionValues) throws DaoException {
		return delete(DBManager.getDefaultDataSourceName(Boolean.FALSE.booleanValue()), entity, conditions, conditionValues);
	}

	private <T> void associate(Class<T> clazz, T t, Boolean doAssociate) {
		if (doAssociate == null)
			doAssociate = Boolean.valueOf(false);
		if (t == null)
			return;
		Object fkValue = null;
		Object associateValue = null;
		Field[] fields = clazz.getDeclaredFields();
		for (Field f : fields)
			if (f.isAnnotationPresent(Associate.class)) {
				Associate associate = (Associate) f.getAnnotation(Associate.class);
				String fk = associate.fk();
				if (StringUtils.isBlank(fk))
					continue;
				try {
					Class<?> c = f.getType();
					fkValue = BeanUtils.getProperty(t, fk);
					associateValue = readById(c, fkValue, doAssociate);
					if (associateValue != null)
						BeanUtils.setProperty(t, f.getName(), associateValue);
				} catch (Exception e) {
					log.warn(e.getMessage(), e);
				}
			}
	}

	private boolean execute(String dataSourceName, Entity entity, boolean isUpdate, String conditions, Object[] conditionValues) throws DaoException {
		if (entity == null) {
			throw new DaoException("Entity is null");
		}

		String executeMethod = "INSERT";
		if (isUpdate) {
			executeMethod = "UPDATE";
		}
		String tableName = getTableName(entity.getClass());

		if (StringUtils.isBlank(tableName)) {
			throw new DaoException("table name is null where " + executeMethod + " with dao");
		}
		String sql = null;
		List<Object> params = new ArrayList<Object>();
		try {
			StringBuffer sqlBuffer = new StringBuffer(executeMethod);
			sqlBuffer.append(" ");
			sqlBuffer.append(tableName);
			sqlBuffer.append(" SET ");

			keyAndParams(entity, sqlBuffer, params);

			sql = sqlBuffer.substring(0, sqlBuffer.length() - 2);
			if (isUpdate) {
				sql = sql + " WHERE " + conditions;
				for (Object o : conditionValues) {
					params.add(o);
				}
			}

			if (params.size() > 0) {
				return DBHelper.update(dataSourceName, sql, params.toArray()) > 0;
			}
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			throw new DaoException("create and execute " + executeMethod + " sql error", e);
		}
		return false;
	}

	private String keyAndParams(Entity entity, StringBuffer sqlBuffer, List<Object> params) throws Exception {
		String field = null;
		Object value = null;

		Class entityClass = entity.getClass();
		Map<String, PropertyDescriptor> map = BeanUtils.propertyDescriptorsWithFiles(entityClass);
		Iterator<String> fields = map.keySet().iterator();
		StringBuffer usedFields = new StringBuffer();
		Field f = null;
		while (fields.hasNext()) {
			field = (String) fields.next();
			f = entityClass.getDeclaredField(field);
			if (f.isAnnotationPresent(Associate.class)) {
				continue;
			}
			value = BeanUtils.getProperty(entity, field);

			sqlBuffer.append(field).append(" = ?, ");
			usedFields.append(field).append(",");
			if ((value instanceof String)) {
				if (StringUtils.isBlank((String) value))
					params.add(null);
				else
					params.add(value);
			} else {
				params.add(value);
			}
		}

		return usedFields.deleteCharAt(usedFields.length() - 1).toString();
	}

	private String getTableName(Class<?> clazz) {
		if (clazz.isAnnotationPresent(Table.class)) {
			Table e = (Table) clazz.getAnnotation(Table.class);
			return e.name();
		}
		return null;
	}

	private String getCountSql(String sql) {
		String searchValue = " from ";
		String sqlLowerCase = sql.toLowerCase();
		if (sqlLowerCase.indexOf("union") > 0) {
			return "select count(*) from ( " + sql + " ) ___TMP";
		}

		int start = sqlLowerCase.indexOf(searchValue) + searchValue.length();
		return "select count(*) from " + sql.substring(start);
	}

	public static void main(String[] args) throws Exception {
		Dao dao = new Dao();
		User u = (User) dao.readById(User.class, "402881e943b3f68f0143b3f68f060000");
		System.out.println(u);
		u = (User) dao.readById(User.class, "402881e943b3f68f0143b3f68f060000");
	}
}